Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Retrieving result sets using proc-text-buffer

The proc–text–buffer technique offers one approach to access results returned from a data source. The following information is the partial syntax for the DEFINE BUFFER statement that you use to create a buffer with the same characteristics of the proc–text–buffer:

Syntax

DEFINE BUFFER buffer-name FOR proc-text-buffer

For a complete description, see the reference entry that discusses the DEFINE BUFFER statement in OpenEdge Development: Progress 4GL Reference .

Technique to use proc-text-buffer

The 4GL procedure in Example 3–6 shows the results of the first stored procedure, pcust, which is Example 3–3 as presented earlier in this section.

/* pcust stored procedure */ DEFINE BUFFER cust-state-join-buffer for proc-text-buffer. RUN STORED-PROC pcust (20, output 0, output 0). FOR EACH cust-state-join-buffer:  DISPLAY cust-state-join-buffer. END. CLOSE STORED-PROC pcust. DISPLAY pcust.orders pcust.states.

Example 3–6: pcust stored procedure

The Progress-defined buffer, proc–text–buffer, has one character field named proc–text. The buffer accepts the returned database results, converts them to CHARACTER data type, and concatenates them into one string.

The next example, Example 3–7, illustrates returning database results into the proc–text-buffer and converting the results to the INTEGER data type.

DEFINE VAR handle1 AS INTEGER. DEFINE VAR imax as INT. RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE  ("SELECT max (cust_num) FROM customer"). FOR EACH proc-text-buffer:   imax = int (substring (proc-text, 1, 3)).    DISPLAY imax. END. CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

Example 3–7: Returning database results into the proc-text-buffer and results conversion

The DataServer passes the SQL statement directly to the MSS data source. The Progress Compiler does not process it, so errors occur only at run time and not when you compile a procedure.

Note: The 4GL QUOTER function can be useful for quoting values or handling embedded quotes when building SQL statements that will be passed into stored procedures with the send-sql-statement option. For more information on the QUOTER function, see OpenEdge Development: Progress 4GL Handbook .

Assessing result sets obtained from the proc-text-buffer technique

The advantage of using the proc–text–buffer is that you do not have to worry about what kind of data the procedure returns. The buffer accepts any type of data, in any order, and converts it to the character equivalent. Another benefit of the proc–text–buffer is that it can be used to hold the results from all of the SQL statements included in a stored procedure. However, a buffer that you create can hold the results of only one results set record form one result set at a time.

However, a disadvantage in using the proc-text-buffer technique is that it is much more difficult to manipulate the data after you receive it as it requires parsing the data. To act on anything but CHARACTER data, you must extract the data from the buffer and convert it to its original data type before you can use it.